package com.hh.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;

public class ZhiLiaoDao {
    public final static String cfn = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    public final static String url = "jdbc:sqlserver://212.129.155.194:1433;DatabaseName=jcyy_test";
    public final static String user = "sa";
    public final static String password = "ZJzhengg@163.com";
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    /*
    JSONArray  封装为json数据格式对象返回
    分页  不搜索的情况下正常展示第一页数据   搜索后按照搜索条件展示数据并分页  采用 sqlserver的 RowNumber的方式
     */
    public JSONArray getZhiLiaoList(String key, String pageSize, String mzhm) {
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray jsonArray = new JSONArray();
        try {
            Class.forName(cfn);
            con = DriverManager.getConnection(url, user, password);
            if(!pageSize.equals("")) {
                String sql = " select * from (select ROW_NUMBER() OVER(Order by ZXXH) AS RowNumber,* from PD_XMYY where ZXBZ=1 and mzy!=3 and YYBZ=1";

                if (mzhm != null && !mzhm.equals("")) {
                    sql += "and MZHM =" + mzhm;
                }

                if (key != null && !key.equals("")) {
                    sql += " and XMMC  LIKE '%" + key + "%'";
                }
                int page = Integer.parseInt(pageSize);
                sql += "  ) as b where  b.RowNumber BETWEEN (" + page + "-1)*6+1 and " + page + "*6 order by ZXXH asc; ";
                System.out.println(sql);
                statement = con.prepareStatement(sql);
            }else {
                String sql = "select * from PD_XMYY p where ZXBZ=1 and mzy!=3 and YYBZ=1";
                if(mzhm!=null && !mzhm.equals("")){
                    sql += "and MZHM ="+mzhm+" Order by p.zxrq_yy desc";
                }
                System.out.println(sql);
                statement = con.prepareStatement(sql);
            }
            res = statement.executeQuery();
            while (res.next()) {
                JSONObject jo = new JSONObject();
                jo.put("xmmc", res.getString("xmmc"));

                Date zxrq_yy = res.getDate("zxrq_yy");
                if(zxrq_yy==null){
                    jo.put("zxrq_yy", "null");
                }else{
                    String time = simpleDateFormat.format(zxrq_yy);
                    jo.put("zxrq_yy", time);
                }

                jo.put("yysjd", res.getString("yysjd"));
                Date zxrq = res.getDate("zxrq");
                if(zxrq==null){
                    jo.put("zxrq", "null");
                }else{
                    String time1 = simpleDateFormat.format(zxrq);
                    jo.put("zxrq", time1);
                }

                if(res.getInt("hylx")==1){
                    jo.put("hylx", res.getString("zxsb"));
                }else if(res.getInt("hylx")==2){
                    jo.put("hylx", res.getString("zxys"));
                }else if(res.getInt("hylx")==3){
                    jo.put("hylx", res.getString("zxfj"));
                }

                jo.put("jfsl", res.getInt("jfsl"));
                jo.put("zxbz", res.getInt("zxbz"));
                jsonArray.add(jo);
            }
            ZhIlIaoToTalDao zhIlIaoToTalDao = new ZhIlIaoToTalDao();
            jsonArray.add(zhIlIaoToTalDao.getZhiLiaoToTal());
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            try {
                if (res != null) res.close();
                if (statement != null) statement.close();
                if (con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return jsonArray;
    }
}
